package com.xxx.controller;

import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.concurrent.ConcurrentLinkedQueue;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicBoolean;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import com.xxx.bean.ExamOrderInExcel;
import com.xxx.dto.ResponseDTO;
import com.xxx.excel.ExcelExport;
import com.xxx.excel.ExcelRead;
import com.xxx.excel.ExcelRowReader;
import com.xxx.util.CommonUtil;

/**
 * Excel上传导入、下载导入模板、导出到excel相关Controller
 */
@Controller
public class ExcelController extends BaseController {
	
	private static final Logger logger = LoggerFactory.getLogger(ExcelController.class);
	
	// SimpleDateFormat非线程安全
	private static final ThreadLocal<SimpleDateFormat> threadLocal = new ThreadLocal<SimpleDateFormat>();

	/**
     * 上传excel页面初始化
     */
    @RequestMapping("/testUploadExcel")
    public ModelAndView testUploadExcel(){
    	logger.info("enter method testUploadExcel().");
        ModelAndView mav = new ModelAndView("uploadExcel");
        return mav;
    }
    
    /**
	 * 上传excel接口 uploadExcel（只支持xlsx格式的excel上传）
     * @throws Exception 
	 */
	@RequestMapping(value="/uploadExcel", method=RequestMethod.POST)
	@ResponseBody
	public ResponseDTO uploadExcel(HttpServletRequest request, HttpServletResponse response,
			MultipartFile file) throws Exception {
    	logger.info("enter method uploadExcel().");
		if (file != null) {	// 判断上传的文件是否为空
			String fileName = file.getOriginalFilename();	// 文件原名称
			if(StringUtils.isEmpty(fileName)) { // 文件名为空
				logger.error("uploadExcel | 文件名称为空");
				return ResponseDTO.builtFail("file name is empty.");
			}
			logger.info("uploadExcel | 上传的文件原名称:" + fileName);
			
			if(fileName.indexOf(".") == -1) { // 文件类型不存在
				logger.error("file type is empty");
				return ResponseDTO.builtFail("file type is empty.");
			}
			
			String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
			if (!StringUtils.isEmpty(fileType)) {	// 判断文件类型是否为空
				if ("XLSX".equals(fileType.toUpperCase())) {
					/**
					 * 1.解析excel上传内容，把解析结果封装成ExamOrderInExcel对象并保存在queue中
					 */
					// 解析excel后存放记录的queue
			        LinkedBlockingDeque<ExamOrderInExcel> excelRecordQueue= new LinkedBlockingDeque<ExamOrderInExcel>(1000);
			        // 上传出错的行
			        ConcurrentLinkedQueue<String> errorQueue = new ConcurrentLinkedQueue<String>();
			        // 用于和子线程通信-解析是否发生异常
					AtomicBoolean isError=new AtomicBoolean(false);
			        // 用于和子线程通信-解析是否完成
					AtomicBoolean isOk=new AtomicBoolean(false);
					// 创建并启动解析excel的线程
					new Thread(new Runnable() {
						ExecutorService fixedThreadPool = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors()*4);
			  			@Override
			  			public void run() {
			  				//子线程中开启多个子线程
			  				try {
			  					ExcelRead excelRead = ExcelRead.getInstance(false);
			  					//为ExcelRead解析器配置当前业务处理器
			  					excelRead.setRowReader(new ExcelRowReader(0, 0, 1, 0, 0, 2) {
			  				    	@Override
			  				    	public void getRows(final int sheetIndex, int curRowNo, ArrayList<String> rowlist, boolean isEndDoc) throws Exception{
			  				    		if(rowlist!=null) {
			  				    			Set<String> set = new HashSet<String>(rowlist);
			  				    			if(set.size()!=1) {//去除list列表值全部为""的情况
												try {
													// 解析excel，并将其封装成ExamOrderInExcel对象
													ExamOrderInExcel vo = converToExamOrderInExcel(rowlist);
													excelRecordQueue.add(vo);
												} catch (Exception e) {
													int sheetNum=sheetIndex+1;
													curRowNo+=1;
													String errorRow = rowlist.get(curRowNo);
													String errorMsg="sheet"+sheetNum+"-第"+curRowNo+"行【"+errorRow+"】发生异常："+e.getMessage();
													errorQueue.add(errorMsg);
													throw e;
												}
			  				    			}
			  				    		}
			  				    	}
			  				    });
			  		        	excelRead.readExcel(file, fixedThreadPool, 100, 501, null);
			  		        	isOk.set(true);
			  				} catch (Exception e) {
			  					logger.error("excel解析线程异常："+e.getMessage());
			  					isError.set(true);
			  				}finally {
			  					fixedThreadPool.shutdown();
			  					logger.info("excel上传线程池关闭完成");
			  				}
			  			}
			  		}).start();
					
			  		/**
			  		 * 2.主线程从queue获取解析结果执行插入操作
			  		 */
					while (true) {
			        	if(isError.get()) {
			        		//如1.中有异常，则将事物回滚
			        		String msg="excel解析异常：";
			        		if(!errorQueue.isEmpty()) {
			        			msg+=errorQueue.toString();
			        		}
			        		throw new Exception(msg);
			        	}
			        	// 从queue中取出数据，并保存数据库
			        	ExamOrderInExcel vo = excelRecordQueue.poll(5, TimeUnit.SECONDS);
			        	if(vo!=null) {
			        		// 模拟解析后的业务处理
			        		System.out.println(vo.toString());
			        	}
			        	// 解析完成且queue中数据已全部入库主线程将结束
			        	if(isOk.get() && excelRecordQueue.peek()==null) {
			        		logger.info("excel上传完成");
			        		break;	        		
			        	}
			        }
					return ResponseDTO.builtSuccess("excel上传成功");
				} else {
					logger.error("uploadExcel | excel文件类型不是xlsx.");
					return ResponseDTO.builtFail("excel file type is not xlsx.");
				}
			} else {
				logger.error("uploadExcel | excel文件类型为空");
				return ResponseDTO.builtFail("excel file type is empty.");
			}
		} else {
			logger.error("uploadExcel | 没有找到相对应的文件");
			return ResponseDTO.builtFail("excel file is not exists");
		}
	}
	
	/**
	 * 解析Excel，并封装成ExamOrderInExcel对象
	 * @param rowlist
	 * @param optNo
	 * @return
	 */
	private ExamOrderInExcel converToExamOrderInExcel(ArrayList<String> rowlist) throws Exception {
		ExamOrderInExcel vo = new ExamOrderInExcel();
		// 手机号
		String userMobile = rowlist.get(0);
		if(StringUtils.isEmpty(userMobile)
			||userMobile.trim().length()>11
			||!CommonUtil.isNumeric(userMobile.trim())) {
			throw new Exception("手机号格式有误");
		}
		vo.setUserMobile(userMobile.trim());
		
		// 付费类型
		String examTypeName = rowlist.get(1);
		if(StringUtils.isEmpty(examTypeName)) {
			throw new Exception("付费类型为空");
		}
		if(!("经纪人".equals(examTypeName.trim())||"协理".equals(examTypeName.trim()))) {
			throw new Exception("付费类型格式有误");
		}
		String examType = "";
		if("经纪人".equals(examTypeName.trim())) {
			examType = "1";
		} else if("协理".equals(examTypeName.trim())) {
			examType = "2";
		}
		vo.setExamType(examType);
		
		// 购买日期
		String buyDate = rowlist.get(2);
		if(StringUtils.isEmpty(buyDate)||buyDate.trim().length()>10) {
			throw new Exception("购买日期格式有误");
		}
		Date date = this.parseDate(buyDate.trim());
		if(date==null) {
			throw new Exception("购买日期格式有误");
		}
		vo.setBuyDate(date);
		return vo;
	}
	
	/**
	 * 格式化日期
	 * @return
	 */
	private Date parseDate(String dateString) {
		Date date = null;
		try {
			SimpleDateFormat sdf = threadLocal.get();
			if (sdf == null){
				if(dateString.indexOf('-')>0) {
		            sdf = new SimpleDateFormat("yyyy-MM-dd");
		        } else if(dateString.indexOf('/')>0) {
		        	sdf = new SimpleDateFormat("yyyy/MM/dd");
		        } else if(dateString.length()==8) {
		        	sdf = new SimpleDateFormat("yyyyMMdd");
		        } 
				date = sdf.parse(dateString);
			}
		} catch (ParseException e) {
			logger.error("Parse " + dateString + " error!", e);
		}
		return date;
	}

	/**
     * 下载题库导入模板
     */
    @RequestMapping("/downloadTemplate")
	public void downloadTemplate(HttpServletResponse response){
    	logger.info("enter method downloadTemplate().");
        try {
            String fileName = "职业考试付费用户上传模板";
            //设置列标题
    		String[] titles = {"手机号","付费类型（经纪人/协理）","购买日期（yyyy-mm-dd）"};
            this.createExcel(response, fileName, titles);
            logger.info("downloadTemplate 下载完成");
        } catch (Exception ex) {
            logger.error("downloadTemplate 发生异常:", ex);
        }
    }

    /**
	 * 创建excel文档
	 * @param response
	 * @param fileName
	 * @param titles
	 * @throws IOException
	 */
	private void createExcel(HttpServletResponse response, String fileName, String[] titles) throws IOException {
		//1.创建工作簿
		XSSFWorkbook workbook = new XSSFWorkbook();
		try{
			//2.创建工作表
            XSSFSheet sheet = workbook.createSheet(fileName);
            
            //3.设置标题样式
            XSSFCellStyle style = workbook.createCellStyle();
        	style.setAlignment(HorizontalAlignment.CENTER);//水平居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            //创建字体
            XSSFFont font = workbook.createFont();
            font.setBold(true); //是否加粗字体
            font.setFontHeightInPoints((short)10); // 字体大小
            style.setFont(font); //加载字体
            
            //4.设置标题
            XSSFRow row = sheet.createRow(0); // 第1行
            for(int i=0;i<titles.length;i++){
            	XSSFCell cellTemp = row.createCell(i);
                cellTemp.setCellStyle(style); //加载单元格样式
                cellTemp.setCellValue(titles[i]); // 加载标题
	        }
            sheet.setDefaultColumnWidth(20); //设置默认列宽
            
            //5.输出设置要下载的文件的名称
            fileName = new String(fileName.getBytes("UTF-8"),"ISO8859-1");
            response.setContentType("application/force-download");
            response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName + ".xlsx" + "\" ");
            workbook.write(response.getOutputStream());
            response.getOutputStream().flush();
            response.getOutputStream().close();
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
        	workbook.close();
        }
	}
	
	/**
	 * 导出excel
	 */
	@RequestMapping("/exportToExcel")
	public void exportToExcel(HttpServletResponse response) {
		logger.info("enter method exportToExcel()");
		
		// 模拟从数据库里查询要导出到excel里的数据
		List<ExamOrderInExcel> list = new ArrayList<>();
		ExamOrderInExcel vo = new ExamOrderInExcel();
		vo.setUserMobile("18600010001");
		vo.setExamType("经纪人");
		vo.setBuyDate(new Date());
		list.add(vo);
		vo = new ExamOrderInExcel();
		vo.setUserMobile("18600010002");
		vo.setExamType("协理");
		vo.setBuyDate(new Date());
		list.add(vo);
		vo = new ExamOrderInExcel();
		vo.setUserMobile("18600010003");
		vo.setExamType("协理");
		vo.setBuyDate(new Date());
		list.add(vo);

		// 导出到excel
		this.doExport(response, list);
	}

	/**
	 * 导出excel
	 * @param response
	 * @param list
	 */
	private void doExport(HttpServletResponse response, List<ExamOrderInExcel> list) {
		try {
			String fileName = "明细统计";
            String[] headers = {"手机号", "付费类型", "购买日期"};
            List<List<Object>> lists = new ArrayList<>();
            for (ExamOrderInExcel vo: list) {
            	List<Object> filedList = new ArrayList<>();
            	filedList.add(vo.getUserMobile());
            	filedList.add(vo.getExamType());
            	filedList.add(vo.getBuyDate());
                lists.add(filedList);
            }
            ExcelExport.getInstance().export(response, lists, fileName, headers);
            logger.info("doExport 导出完成");
        } catch (Exception e) {
            logger.error("doExport 明细统计导出发生异常，" + e.getMessage(), e);
        }
	}
	
}
